package com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.repository.impl;

import com.css.fxfzfxqh.constants.YNEnum;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.entity.EconomicLossED;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.entity.EconomicLossEDCalculation;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.entity.EconomicLosses;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.param.EconomicLossParam;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.repository.EconomicLossEDRepository;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.vo.EconomicLossPCCVO;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.economicLossDE.vo.EconomicLossXYVO;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.param.AssessParam;
import com.css.fxfzfxqh.modules.largeScreen.entity.Calculation;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/06/09
 * @Description: TODO
 */
@Repository
public class EconomicLossEDRepositoryImpl implements EconomicLossEDRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    //抗震能力_正确评估_评估记录主表
    private static final String TABLE_NAME = "fxqh_economic_loss_assess_ed";
    //抗震能力_正确评估_评估记录_计算(烈度,概率)(地理id,房屋单体id) 合并为一个字段
    private static final String TABLE_CALCULATION = "fxqh_economic_loss_calculation_ed";

    @Override
    public void addAssess(EconomicLossED param) {
        String sql = "insert into "+TABLE_NAME+"(id,assess_code,assess_name,assess_desc,house_data,house_data_flag," +
                "coordinate_range,calculation,create_user,create_time,del_flag,seismic_input_mode,influence_version," +
                "geom,radio2,area_range) " +
                "values(:id,:assessCode,:assessName,:assessDesc,:houseData,:houseDataFlag," +
                ":coordinateRange,:calculation,:createUser,:createTime,:delFlag,:seismicInputMode,:influenceVersion," +
                "ST_GeometryFromText(:geom,4490),:radio2,:areaRange)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public EconomicLossED findById(String id) {
        String sql="select * from "+TABLE_NAME+" where id = ? ";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EconomicLossED.class), id);
    }

    @Override
    public void upDateAssess(EconomicLossED param) {
        String sql = "update " + TABLE_NAME +
                " set assess_name = :assessName,assess_desc = :assessDesc," +
                "update_user=:updateUser,update_time=:updateTime where del_flag = '0' and id = '"+param.getId()+"'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public String queryLatest(StringBuilder code) {
        StringBuilder sql = new StringBuilder("select assess_code from " + TABLE_NAME +" where del_flag = '0' and assess_code like '%"+code+"%' order by create_time desc limit 1");
        try {
            return jdbcTemplate.queryForObject(sql.toString(), String.class);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, AssessParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //评估记录编号
        if (!PlatformObjectUtils.isEmpty(param.getAssessCode())) {
            whereSql.append(" and assess_code = '" + param.getAssessCode() + "'");
        }
        //评估记录名称
        if (!PlatformObjectUtils.isEmpty(param.getAssessName())) {
            whereSql.append(" and assess_name = '" + param.getAssessName() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EconomicLossED.class)).size();
        List<EconomicLossED> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(EconomicLossED.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public void batchCalculation(List<EconomicLossEDCalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO "+TABLE_CALCULATION+" (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,condition,calculated_value,correction_value,coordinate_range,create_user," +
                "create_time,extends1,intensity,geom,condition_type)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:condition,:calculatedValue,:correctionValue,:coordinateRange,:createUser," +
                ":createTime,:extends1,:intensity,ST_GeometryFromText(:geom, 4490),:conditionType)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }
    //根据条件删除计算数据

    @Override
    public void deleteCalculation(String assessId, String seismicInputMode) {
        String sql = "delete from "+TABLE_CALCULATION+" where assess_id='"+assessId+"'";
        if (PlatformObjectUtils.isNotEmpty(seismicInputMode)){
            sql=sql+" and condition_type='"+seismicInputMode+"'";
        }
        jdbcTemplate.update(sql);

    }

    @Override
    public List<EconomicLossPCCVO> getListPCC(EconomicLossParam param) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //条件(地震烈度 或 四个概率水准)
        if (!PlatformObjectUtils.isEmpty(param.getCondition())) {
            whereSql.append(" and condition = '" + param.getCondition() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())){
            whereSql.append(" and province_name = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())){
            whereSql.append(" and city_name = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())){
            whereSql.append(" and county_name = '" + param.getCounty() + "'");
        }
        return jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(EconomicLossPCCVO.class));
    }

    @Override
    public List<EconomicLossXYVO> getListXY(EconomicLossParam param) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        //条件(地震烈度 或 四个概率水准)
        if (!PlatformObjectUtils.isEmpty(param.getCondition())) {
            whereSql.append(" and condition = '" + param.getCondition() + "'");
        }
        return jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(EconomicLossXYVO.class));
    }

    //根据条件查询计算值
    @Override
    public Map<String, Object> queryCalculation(int curPage, int pageSize, String seismicInputMode, String id, String condition) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_CALCULATION+" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(id)) {
            sql.append(" and assess_id = '"+id+"'  ");
        }
        //地震影响场这个参数没存；
        if(!"2".equals(seismicInputMode)){
            sql.append("and condition ='"+condition+"' ");
        }
        //如果是地震影响场 需要加上地震影响场的参数condition_type 为
        else {
            sql.append("and condition_type = '31' ");
        }
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EconomicLossEDCalculation.class)).size();
        List<EconomicLossEDCalculation> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(EconomicLossEDCalculation.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //修改修改经验评估与修正值
    @Override
    public void updateCorrectionValue(EconomicLossEDCalculation param) {
        String sql = "update " + TABLE_CALCULATION + " set correction_value=:correctionValue," +
                "update_user = :updateUser ,update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public EconomicLosses getDefaultModel() {
        String sql = "select * from fxqh_economic_losses where is_valid ='" + YNEnum.Y.toString() +
                "' and is_default='" + YNEnum.Y.toString() + "' limit 1";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EconomicLosses.class));
    }

    @Override
    public List<Map<String, Object>> getSumByDivisionAndAssessCodes(String province, String city, String county,
                                                       String assessCodes, String condition) {
        String sql="select sum(cast(t1.correction_value as float)) sum,count(t1.assess_id) count from fxqh_economic_loss_calculation_ed t1" +
                " left join (SELECT * FROM fxqh_calculation_tasks WHERE del_flag = '0' AND id='"+assessCodes+"') t2 on t1.assess_id=t2.id" +
                " where t1.province_name='"+province+"' and t1.city_name='"+city+"' and t1.county_name='"+county+"'" +
                " and t1.condition='"+condition+"' and t2.del_flag='0'" +
                " group by t1.extends1 order by count(t1.assess_id) desc";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public Boolean getSTIntersects(String geomText, BigDecimal longitude, BigDecimal latitude) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490))";

        return jdbcTemplate.queryForObject(sql, Boolean.class, geomText, longitude, latitude);
    }

    @Override
    public List<Calculation> getValue(List<String> taskIdList, String code){
        String sql = "";
        if("0".equals(code)){
            sql = "select sum(correction_value)/10000 val,province_name areaName,case when intensity='6' then '1'  when intensity='7' then '2' " +
                    " when intensity='8' then '3'  when intensity='9' then '4'  when intensity='10' then '5' else 0 end intensity from fxqh_economic_loss_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') and intensity in ('6','7','8','9','10') GROUP BY intensity,province_name order by val";
        }else{
            sql = "select sum(correction_value)/10000 val,intensity from fxqh_economic_loss_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') GROUP BY area_name order by val";
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Calculation.class));
    }

    @Override
    public List<EconomicLossEDCalculation> getCasualtiesResult(String taskId, String province, String condition, String seismicInputMode) {
        StringBuilder sql = new StringBuilder("select * from "+TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1").append(" and condition = '").append(condition).append("'");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(taskId)) {
            whereSql.append(" and assess_id = '").append(taskId).append("'");
        }
//        //条件(地震烈度 或 四个概率水准)
//        if (!PlatformObjectUtils.isEmpty(condition)) {
//            whereSql.append(" and condition = '").append(condition).append("'");
//        }
        if (PlatformObjectUtils.isNotEmpty(province)){
            whereSql.append(" and province_name = '").append(province).append("'");
        }
        return jdbcTemplate.query(sql.append(whereSql).toString(),new BeanPropertyRowMapper<>(EconomicLossEDCalculation.class));
    }
}
